package com.lms.dao.lab;

import com.lms.dao.BaseDao;
import com.lms.bean.LabData;
import com.lms.bean.LabWeek;
import com.lms.pojo.LabIntervalData;
import com.lms.service.lab.LabService;
import com.lms.service.lab.LabServiceImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class LabDaoImpl implements LabDao{
    private PreparedStatement pstm=null;
    private ResultSet rs=null;
    @Override
    public List<LabData> getLabList(Connection connection) throws SQLException {
        List<LabData> labDataList =new ArrayList<>();

        if (connection!=null){
            String sql="select * from lab_data";
            Object[] params={};

            rs=BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                LabData _labData =new LabData();
                _labData.setLabCode(rs.getString("labCode"));
                _labData.setLabName(rs.getString("labName"));
                _labData.setLabPicture(rs.getString("labPicture"));
                _labData.setLabIntroduce(rs.getString("labIntroduce"));
                _labData.setLabTag(rs.getInt("labTag"));
                _labData.setLabTotal(rs.getInt("labTotal"));
                labDataList.add(_labData);
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return labDataList;
    }

    @Override
    public LabWeek getLabWeekByLabName(Connection connection,String LabCode,String week) throws SQLException {
        LabWeek labWeek=null;

        if (connection!=null){
            String sql="select * from "+week+" where labCode=?";
            Object[] params={LabCode};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                labWeek = new LabWeek();
                labWeek.setLabCode(rs.getString("labCode"));
                labWeek.setOne(rs.getInt("one"));
                labWeek.setTwo(rs.getInt("two"));
                labWeek.setThree(rs.getInt("three"));
                labWeek.setFour(rs.getInt("four"));
                labWeek.setFive(rs.getInt("five"));
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return labWeek;
    }

    @Override
    public int getLabTotal(Connection connection, String labCode) throws SQLException {
        int total = 0;

        if (connection!=null){
            String sql="select labTotal from lab_data where labCode=?";
            Object[] params={labCode};

            rs=BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                total=rs.getInt("labTotal");
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return total;
    }

    @Override
    public int getApplyCount(Connection connection,String labCode, String week, String interval) throws SQLException {
        int applyCount=0;

        if (connection!=null){
            String sql="select sum(apply_applyCount) as applyCount from user_apply " +
                    "where labCode=? and  apply_time_week=? and apply_time_interval=? and (apply_state=1 or apply_state=3)";
            Object[] params={labCode,week,interval};
            rs=BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                applyCount=rs.getInt("applyCount");
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return applyCount;
    }

    @Override
    public LabData getLabDataByLabCode(Connection connection, String labCode) throws SQLException {
        LabData labData=null;

        if (connection!=null){
            String sql="select * from lab_data where labCode=?";
            Object[] params={labCode};
            rs=BaseDao.execute(connection,pstm,rs,sql,params);
            if (rs.next()){
                labData=new LabData();
                labData.setLabCode(labCode);
                labData.setLabName(rs.getString("labName"));
                labData.setLabPicture(rs.getString("labPicture"));
                labData.setLabIntroduce(rs.getString("labIntroduce"));
                labData.setLabTag(rs.getInt("labTag"));
                labData.setLabTotal(rs.getInt("labTotal"));
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return labData;
    }

    @Override
    public List<LabIntervalData> getLabIntervalData(Connection connection, String week, String interval) throws SQLException {
        List<LabIntervalData> labIntervalDataList=new ArrayList<>();

        if (connection!=null){
            String sql="select lab.labCode,labName,"+interval+" as open from lab_data as lab inner join "+"lab_"+week+" as week where lab.labCode=week.labCode";
            Object[] params={};
            LabService labService = new LabServiceImpl();
            rs=BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                LabIntervalData labIntervalData = new LabIntervalData();
                labIntervalData.setLib(rs.getString("labName"));
                labIntervalData.setCapacity(labService.getLabTotalAndCapacity(rs.getString("labCode"),week,interval)[1]);
                if (rs.getInt("open")==1){
                    labIntervalData.setOpen("是");
                }else {
                    labIntervalData.setOpen("否");
                }
                labIntervalDataList.add(labIntervalData);
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return labIntervalDataList;
    }

    @Override
    public String getLabCodeByLabName(Connection connection, String labName) throws SQLException {
        String labCode=null;
        if (connection!=null){
            String sql="select labCode from lab_data where labName=?";
            Object[] params={labName};
            rs = BaseDao.execute(connection, pstm, this.rs, sql, params);

            if (rs.next()){
                labCode=rs.getString("labCode");
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return labCode;
    }

    @Override
    public LabData getLabDataByLabName(Connection connection, String labName) throws SQLException {
        LabData labData=new LabData();
        if (connection!=null){
            String sql="select * from lab_data where labName=?";
            Object[] params={labName};
            rs = BaseDao.execute(connection, pstm, this.rs, sql, params);

            while (rs.next()){
                labData.setLabCode(rs.getString("labCode"));
                labData.setLabName(rs.getString("labName"));
                labData.setLabIntroduce(rs.getString("labIntroduce"));
                labData.setLabPicture(rs.getString("labPicture"));
                labData.setLabTag(rs.getInt("labTag"));
                labData.setLabTotal(rs.getInt("labTotal"));
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return labData;
    }

    @Override
    public int labModify(Connection connection, String labName, String labTotal, String labTag, String labIntroduce) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="update lab_data set labName=?,labTotal=?,labTag=?,labIntroduce=? where labName=?";
            Object[] params={labName,labTotal,labTag,labIntroduce,labName};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection,pstm,rs);
        }
        return res;
    }
}
